Simple Log Service:Connect to SLS using the PostgreSQL protocol

PostgreSQL client connection URL format:

postgresql://{AccessKey_ID}:{AccessKey_Secret}@{Endpoint}:5432/{Project_Name}
# The connection enables TLS by default (MD5 for authentication). To explicitly disable TLS (for example, if the client does not support it), set the sslmode parameter to disable.
postgresql://{AccessKey_ID}:{AccessKey_Secret}@{Endpoint}:5432/{Project_Name}?sslmode=disable

JDBC connection example:

private static final String URL = "jdbc:postgresql://{Endpoint}:5432/{Project_Name}?preferQueryMode=simple";
private static final String AccessKey_ID = "xyz";
private static final String AccessKey_Secret = "123456";

public static void main(String[] args) throws Exception
{
    Properties props = new Properties();
    props.setProperty(PGProperty.USER.getName(), AccessKey_ID);
    props.setProperty(PGProperty.PASSWORD.getName(), AccessKey_Secret);

    try (Connection conn = DriverManager.getConnection(URL, props)) {
        System.out.println("Connection successful!");
    }
}

Parameters

Create a table

Create a logstore with CREATE TABLE:

CREATE TABLE users (
    id           BIGINT,
    name         TEXT,
    create_time  TIMESTAMP
)
WITH (
    shard_count = 4,
    ttl = 14,
    default_query_range = '1d',
    full_text_search_enabled = true,

    index_tokens = '&[ ]",''\',

    log_time_field = 'create_time',
    insert_any_allowed = false
);

Supported column types:

Table properties (WITH clause):

Modify a column

Supported column modifications:

Add a column:

ALTER TABLE users ADD COLUMN change_time TEXT;

Rename a column:

ALTER TABLE users RENAME COLUMN change_time TO update_time;

Modify a column type:

ALTER TABLE users ALTER COLUMN update_time TYPE TIMESTAMP;

Delete a column:

ALTER TABLE users DROP COLUMN update_time;

Clear data

Clear all data from a table with TRUNCATE:

TRUNCATE TABLE users;

Note : After data is deleted, it takes about 30 seconds for the change to become visible in SQL queries.

Delete a table

Delete a table and its corresponding logstore with DROP TABLE:

DROP TABLE users;

Basic query

Query data with SELECT:

SELECT id, name FROM users LIMIT 10;

Note : If no time range is specified in the SQL statement, the default time range defined by default_query_range is used. If default_query_range was not set when the table was created, the query defaults to the last 15 minutes of data.

Specify a query time range

Two ways to specify a query time range:

-- Specify the lower limit of the time range
SELECT id, name FROM users 
WHERE __time__ > 1766646000
LIMIT 10;

-- Specify the time range
SELECT id, name FROM users 
WHERE __time__ > 1766646000 AND __time__ < 1766646987
LIMIT 10;

-- Set the lower limit of the time range
SET query_begin = 1766646000; 
SELECT id, name FROM users LIMIT 10;

-- Set the time range
SET query_begin = 1766646000; 
SET query_end = 1766646987; 
SELECT id, name FROM users LIMIT 10;

-- Set the variables in a single statement (recommended)
SET query_begin = 1766646000; SET query_end = 1766646987; SELECT id, name FROM users LIMIT 10;

Basic write

Write data with INSERT INTO:

INSERT INTO users (id, name, create_time) 
VALUES
    (1, 'Name#1', '2025-12-25T06:54:19'),
    (2, 'Name#2', '2025-12-25T06:54:20'),
    (3, 'Name#3', '2025-12-25T06:54:21');

EXACTLY-ONCE writes

SLS does not support primary key uniqueness constraints but provides EXACTLY-ONCE semantics to prevent duplicate writes.

Set hash_key and seq_id to enable EXACTLY-ONCE writes:

SET hash_key = 'file#1'; 
SET seq_id = 1; 
INSERT INTO users (id, name, create_time) 
VALUES
    (1, 'Name#1', '2025-12-25T06:54:19'),
    (2, 'Name#2', '2025-12-25T06:54:20'),
    (3, 'Name#3', '2025-12-25T06:54:21');

Parameters

Duplicate data error handling:

On duplicate writes, the server returns an error:

error: sequence id is lower than expected, hash_key=xyz,current sequence_id=100,requested sequence_id=90

Extract the current seq_id with this regex:

current\s+sequence_id=(\d+)

Query all logstores

SELECT 
    table_schema, 
    table_name
FROM 
    information_schema.tables;

Note : By default, a logstore is mapped to a schema under a project of the same name.

Query the field structure

SELECT 
    column_name, 
    data_type
FROM 
    information_schema.columns 
WHERE 
    table_name = 'access_log';