Simple Log Service:Connect to SLS using the PostgreSQL protocol

To connect to SLS using a PostgreSQL client, use the following 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

To connect to SLS using the PostgreSQL protocol through JDBC, use the following configuration:

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!");
    }
}

Parameter description:

Create a table

Use the CREATE TABLE statement to create a logstore:

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

The following column modification operations are supported:

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

Use the TRUNCATE statement to clear all data from a table:

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

Use the DROP TABLE statement to delete a table (which also deletes the corresponding logstore):

DROP TABLE users;

Basic query

Use the standard SQL SELECT statement to query data:

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

Specify a query time range in one of the following two ways:

-- 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

Use the INSERT INTO statement to write data:

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

The SLS PostgreSQL protocol does not support uniqueness constraints such as primary keys, but it supports EXACTLY-ONCE writes to ensure data is written only once.

Use the hash_key and seq_id variables to implement 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');

Parameter description:

Duplicate data error handling:

When duplicate data is written, the server returns an error message in the following format:

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

Use the following regular expression to extract the current seq_id from the server response:

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';